﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace hdb {
   public class PageDialect
    {
        public static String pageSql(GKDBType dbType,string sql, int start, int end, string orderBy) {
            if (GKDBType.oracle == dbType) {
                return createOraclePageSql( sql,  start,  end,  orderBy);
            }
            if (GKDBType.sqlserver == dbType)
            {
                return createSqlserverPageSql(sql, start, end, orderBy);
            }

            if( GKDBType.mySql == dbType ) {
                return createMysqlPageSql(sql ,start ,end ,orderBy);
            }
            return "";
        }
        static  String createOraclePageSql(string sql, int start, int end, string orderBy)
        {
            sql = @"SELECT *  FROM (SELECT tt.*, ROWNUM AS rowno  FROM (  SELECT t.* FROM " + sql;
            sql += @" ORDER BY " + orderBy + ") tt WHERE ROWNUM <= " + end + ") table_alias WHERE table_alias.rowno >= " + start;
            String countSql = "select COUNT(*) FROM (" + sql + ") as t";
            return sql + ";" + countSql;
        }

        static String createMysqlPageSql(string sql ,int start ,int end ,string orderBy) {
            start -= 1;
            String countSql = sql.ToUpper();
            countSql= countSql.Substring(countSql.IndexOf("FROM"));
            countSql = "select  COUNT(*) " + countSql;

            sql += @" ORDER BY " + orderBy + " LIMIT " + start+","+(end-start);
      
            return sql + ";" + countSql;
        }
        static  String createSqlserverPageSql(string sql, int start, int end, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlCount = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderBy))
            {
                strSql.Append("order by T." + orderBy);
            }
            strSql.Append(")AS pRowno, T.*  from (");
            strSql.Append(sql);
            strSql.Append(")T ) TT");
            strSql.AppendFormat(" WHERE TT.pRowno>= {0} and  TT.pRowno<={1}  ORDER BY TT.pRowno " , start, end);
            strSqlCount.Append("select COUNT(*) FROM (" + sql + ") as t");
            return strSql.ToString() + ";" + strSqlCount.ToString();
        }
    }
}
